Oracle语法：merge into

发表于 | 分类于 ， |
| 字数统计: 896

Oracle在9i引入了merge语法, 通过merge语句能够在一个SQL语句中对一个表同时执行inserts和updates操作。merge into也是一个dml语句，和其他的dml语句一样需要通过rollback和commit 结束事务。

merge into语句的用法

merge into 语句使用的场景：存在匹配条件的记录则更新，不存在则插入新的记录。

语法格式：

1
2
3
4
5
6
7
8
9
10
11
MERGE INTO 表名 别名1 
USING (table|view|sub_query) 别名2
ON (JOIN condition)
WHEN MATCHED THEN 
    --如果存在，更新
    UPDATE SET 
    别名1.col1 = 别名2.col, 
    别名1.col2 = 别名2.col
WHEN NOT MATCHED THEN
    --如果不存在，新增
    INSERT (column1, column2) VALUES (别名2.column1, 别名2.column2);

判断Ｂ表和Ａ表是否满足ON中条件，如果满足则执行满足后面的update SQL，如果不满足，则执行后面的insert SQL。

举例如下：

1
2
3
4
5
6
7
8
9
MERGE INTO  TEST T1

USING (SELECT '2' as ID, 'aaa' as NAME FROM dual) T2 

ON (T1.ID=T2.ID)

WHEN MATCHED THEN UPDATE SET T1.NAME=T2.NAME

WHEN NOT MATCHED THEN INSERT (T1.ID, T1.NAME) VALUES (T2.ID, T2.NAME )

UPDATE或INSERT子句是可选的

在9i里由于必须insert into和update都要存在，也就要么执行update否则执行insert，不支持单一的操作，所以使用起来还不太方便。而10g里就是可选了，可以只有update或insert语句，这样能符合我们更多的需求了 。比如上面的语句可以只写一个update部分。

1
2
3
4
5
6
7
8

MERGE INTO  TEST T1

USING (SELECT '2' as ID, 'aaa' as NAME FROM dual) T2 

ON (T1.ID=T2.ID)

WHEN MATCHED THEN UPDATE SET T1.NAME=T2.NAME

UPDATE和INSERT子句可以加WHERE子句

这是一个功能性的改进，能够更加方面应对实际场景，where条件语句很明显是用来过滤的，只对满足where条件的才会进行update或者insert。
下面这条sql是update加where条件的例子：

1
2
3
4
merge into products p using (select * from newproducts) np on (p.product_id = np.product_id) 
when matched then 
update set p.product_name = np.product_name where np.product_name like 'OL%' 
--这里表示只是对product_name开头是'OL'的匹配上的进行update，如果开头不是'OL'的就是匹配到了也不会做操作

当然，这个where条件如果直接放在using语句中效果也是一样的。

同样的，insert语句里也可以使用where，比如：

1
2
3
4
5
merge into products p using (select * from newproducts) np on (p.product_id = np.product_id) 
when matched then 
update set p.product_name = np.product_name where np.product_name like 'OL%' 
when not matched then 
insert values(np.product_id, np.product_name, np.category) where np.product_name like 'OL%'

UPDATE子句后面加DELETE字句

UPDATE子句后面还可以加DELETE子句来删除一些不需要的行。注意：DELETE只能和UPDATE配合，不能和INSERT一起使用。
例如：

1
2
3
4
5
6
merge into products p using (select * from newproducts) np on (p.product_id = np.product_id) 
when matched then 
update set p.product_name = np.product_name delete where p.product_id = np.product_id where np.product_name like 'OL%' 
when not matched then 
insert values(np.product_id, np.product_name, np.category) 
--这里的结果是会把匹配的记录的prodcut_name更新到product里，并且把product_name开头为OL的删除掉。

mybatis中批量merge into

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<update id="mergeinto">
    merge into user_type a
    using (
    <foreach collection="list" item="item" separator="union all">
        select
        #{item.name,jdbcType=VARCHAR} as name,
        #{item.type,jdbcType=VARCHAR} as type
        from dual
    </foreach>
    ) b
    on (a.type = b.type)
    when matched then
        update set name = #{name}
    when not matched then
        insert  (type,name) values(#{type},#{name})
</update>

注意从list中的对象取字段的值是用#{item.name}方式，而不是直接用#{name}。

Mapper.java文件中代码是这样写的：

1
2
3
4
5

@Mapper
public interface UserTypeMapper {
    void mergeinto(@Param("list") List<UserType> list);
}

------ 本文完 ------